/* global process */
import mysql from 'mysql2/promise'
import dotenv from 'dotenv'

dotenv.config()

// 验证必要的环境变量
const requiredEnvVars = ['DB_HOST', 'DB_PORT', 'DB_USER', 'DB_PASSWORD', 'DB_NAME']
const missingVars = requiredEnvVars.filter(varName => !process.env[varName])

if (missingVars.length > 0) {
  console.error(
    '❌ 缺少必要的环境变量:\n' +
    missingVars.map(v => `   - ${v}`).join('\n') +
    '\n\n请确保 server/.env 文件存在并包含以下配置:\n' +
    '   DB_HOST=localhost\n' +
    '   DB_PORT=3306\n' +
    '   DB_USER=your_username\n' +
    '   DB_PASSWORD=your_password\n' +
    '   DB_NAME=SoftwareManagementExp\n'
  )
  process.exit(1)
}

// 数据库连接池配置
const poolConfig = {
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
}

// 创建连接池
export const pool = mysql.createPool(poolConfig)

// 测试数据库连接
export async function testConnection() {
  try {
    const connection = await pool.getConnection()
    console.log('✅ MySQL 数据库连接成功')
    connection.release()
    return true
  } catch (error) {
    console.error('❌ MySQL 数据库连接失败:', error.message)
    return false
  }
}

// 执行查询的辅助函数
export async function query(sql, params) {
  try {
    const [rows] = await pool.execute(sql, params)
    return rows
  } catch (error) {
    console.error('数据库查询错误:', error)
    throw error
  }
}

// 事务辅助函数
export async function transaction(callback) {
  const connection = await pool.getConnection()
  await connection.beginTransaction()
  try {
    const result = await callback(connection)
    await connection.commit()
    return result
  } catch (error) {
    await connection.rollback()
    throw error
  } finally {
    connection.release()
  }
}
